﻿CREATE PROCEDURE [dbo].[sp_Get_AccountsHistory]
(
	@Year INT
)
AS
	SELECT zo.AccountID, a.AgentNumber, zi.TotalZips, 
	zo.Jan, zo.Feb, zo.Mar, zo.Apr, zo.May, zo.Jun,
	zo.Jul, zo.Aug, zo.Sep, zo.Oct, zo.Nov, zo.Dec, a.FirstName + ' ' + a.LastName
	FROM
	(
		SELECT a.AccountID, 
		SUM(CASE WHEN o.PeroidMonth = 1 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Jan,
		SUM(CASE WHEN o.PeroidMonth = 2 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Feb,
		SUM(CASE WHEN o.PeroidMonth = 3 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Mar,
		SUM(CASE WHEN o.PeroidMonth = 4 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Apr,
		SUM(CASE WHEN o.PeroidMonth = 5 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS May,
		SUM(CASE WHEN o.PeroidMonth = 6 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Jun,
		SUM(CASE WHEN o.PeroidMonth = 7 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Jul,
		SUM(CASE WHEN o.PeroidMonth = 8 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Aug,
		SUM(CASE WHEN o.PeroidMonth = 9 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Sep,
		SUM(CASE WHEN o.PeroidMonth = 10 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Oct,
		SUM(CASE WHEN o.PeroidMonth = 11 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Nov,
		SUM(CASE WHEN o.PeroidMonth = 12 AND o.OrderID IS NOT NULL THEN 1 ELSE 0 END) AS Dec
		FROM  dbo.Accounts a
		LEFT JOIN (SELECT * FROM dbo.Orders WHERE PeroidYear = @Year and IsPurchased = 1) o
		ON o.AccountID = a.AccountID
		WHERE a.AccountType = 0 AND a.Status <> 2
		GROUP BY a.AccountID
	)zo INNER JOIN
	(
		select MdNumber, count(*) as TotalZips from dbo.ZipInfo group by MdNumber 
	)zi ON zo.AccountID = zi.MdNumber
	INNER JOIN dbo.Accounts a
	ON a.AccountID = zo.AccountID ORDER BY zo.AccountID
